insert overwrite table res_oil
select minutes,stationID,oils,SUM(oilNumber),SUM(oilLitre),SUM(oilMoney)
from (
      select substr(X.transaction_date,1,16) minutes,X.station_id stationID,X.product_code oils,count(X.product_code) oilNumber,sum(X.item_quantity) oilLitre,sum(X.item_value) oilMoney
      from
      (select Z.transaction_date,Y.station_id,Y.transaction_id,Y.product_code,Y.item_quantity,Y.item_value
      from mid.transactions as Z
      join mid.transaction_items_oil as Y
      on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
      where Y.item_type!=20 and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)>-24*3600
and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)<24*3600)AS X
      group by substr(X.transaction_date,1,16),X.station_id,X.product_code
      union all
      SELECT minutes,stationID,oils,oilNumber,oilLitre,oilMoney from res_oil_history
) tmp GROUP BY minutes,stationID,oils ;

insert overwrite table res_notoil
select SUBSTR(transaction_date,1,16) minutes,station_id,count(DISTINCT transaction_id) notoilnumber,sum(X.item_value) notoilmoney
from
(select Z.transaction_date,Y.station_id,Y.transaction_id,Y.item_value
from mid.transactions as Z
join mid.transaction_items_notoil as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
where unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)>-24*3600
and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)<24*3600)AS X
group by SUBSTR(transaction_date,1,16),station_id
union  all
select minutes,stationID station_id,notoilnumber,notoilmoney from res_notoil_history;

insert overwrite table res_notoilDepartment
select substr(X.transaction_date,1,16),X.station_id,X.dept_name,count(X.department_id),sum(X.item_value)
from
(select Z.transaction_date,Y.station_id,Y.transaction_id,Y.item_value,Y.department_id,C.dept_name
from mid.transactions as Z 
join mid.transaction_items_notoil as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
join mid.products as B
on Y.product_code = B.product_code and  B.station_id = 50006
join mid.departments as C
on C.station_id = 50006 and B.department_id = C.department_id
where unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)>-24*3600
and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)<24*3600
)AS X
group by
substr(X.transaction_date,1,16),X.station_id,X.dept_name;

insert overwrite table mid_notoilTop
select substr(X.transaction_date,1,10),X.station_id,X.item_description,X.product_code,sum(X.item_quantity),sum(X.item_value)
from
(select Y.transaction_id,Z.transaction_date,Y.station_id,Y.item_description,Y.item_value,Y.product_code,Y.item_quantity
from mid.transactions as Z 
join mid.transaction_items_notoil as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
where unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)>-24*3600
and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)<24*3600)AS X
group by substr(X.transaction_date,1,10),X.station_id,X.item_description,X.product_code;

insert overwrite table res_notoilTop
select * from
(select minutes,stationID,top10,productCode,number,money,
row_number() over (partition by minutes,stationID order by number desc)AS rank from mid_notoilTop a
)b
where b.rank<=10;

insert OVERWRITE table mid_lube
select DISTINCT d.transaction_date,d.station_id,d.transaction_id,d.item_value,d.oillitre from 
(
select A.transaction_id as transaction_id,C.transaction_date as transaction_date ,A.station_id as station_id,B.department_id as department_id,A.item_value as item_value ,trim(A.item_description)as oillitre
from mid.transaction_items_notoil as A
join mid.products as B
on A.product_code = B.product_code and B.station_id = 50006
join mid.transactions as C
on C.transaction_id = A.transaction_id  and C.station_id=A.station_id
where unix_timestamp(A.transaction_date)-unix_timestamp(C.transaction_date)<24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(C.transaction_date)>-24*3600
)d where d.department_id in (548,64);

insert overwrite table res_lube
select minutes,stationID,sum(lubenumber),sum(lubelitre),sum(lubemoney) from
(
     select * from res_lube_history
     union all
     select substr(minutes,1,16) minutes,stationID,count(DISTINCT transactionID) lubenumber,sum(substr(oillitre,-2,1)) lubelitre,sum(itemvalue) lubemoney
     from mid_lube
     group by substr(minutes,1,16),stationID
) a group by minutes,stationID;

-- insert overwrite table mid_notoilRate
-- select substr(transaction_date,1,13),station_id,count(*)
-- from mid.transactions
-- group by substr(transaction_date,1,13),station_id;
--
-- insert overwrite table res_notoilRate
-- select A.houra,A.stationID,A.numbera,B.numberb
-- FROM
-- (select SUBSTR(minutes,1,13)AS houra,stationID,sum(notoilNumber)AS numbera
-- from res_notoil
-- group by SUBSTR(minutes,1,13),stationID) AS A
-- JOIN
-- (select substr(minutes,1,13)AS hourb,stationID,sum(tradeNumber)AS numberb
-- from mid_notoilRate
-- group by substr(minutes,1,13),stationID) AS B
-- ON A.houra=B.hourb and A.stationID=B.stationID;

insert overwrite table res_notoilRate
SELECT substr(minutes,1,13),stationID,
sum(case when is_oil=1 then 0 else numbera end)
,sum(numbera) from
(SELECT minutes,stationID,oilNumber numbera,1 is_oil from res_oil
union all
select minutes,stationID,notoilNumber numbera,0 is_oil
from res_notoil) tmp
group by SUBSTR(minutes,1,13),stationID;



INSERT OVERWRITE TABLE res_oilPrice
select Y.effective_date,Y.station_id,Z.Grade_Name,Y.grade_price
from mid.Grades as Z 
JOIN mid.future_grade_price as Y 
on Z.Grade_ID = Y.grade_id and Z.station_id=Y.station_id
order by Y.effective_date;

insert overwrite table res_oilPrice
select time,stationID,
case when trim(oils) = '-10# 柴油' then '-10#'
     when trim(oils) = '-20# 柴油' then '-20#'
     when trim(oils) = '-35#柴油' then '-35#'
     when trim(oils) = '0# 柴油' or oils = '0#柴油' then '0#'
     when trim(oils) = '92#京标(VI)车用汽油' or oils = '92#京标(VI)车用汽油.' or oils = '92#汽油' then '92#'
     when trim(oils) = '95#汽油' or oils = '95#京标(VI)车用汽油' then '95#'
else oils end,
oilprice from res_oilPrice 
where trim(oils) = '-10# 柴油'
or trim(oils) = '-20# 柴油'
or trim(oils) = '-35#柴油'
or trim(oils) = '0# 柴油'
or trim(oils) = '0#柴油'
or trim(oils) = '92#京标(VI)车用汽油'
or trim(oils) = '92#京标(VI)车用汽油.'
or trim(oils) = '92#汽油'
or trim(oils) = '95#京标(VI)车用汽油'
or trim(oils) = '95#汽油';

insert overwrite table res_mop
select substr(X.transaction_date,1,10),X.station_id,X.mop_name,count(X.item_quantity),sum(X.item_value)
from
(select Z.transaction_date,Y.station_id,Y.mop_name,Y.item_value,Y.item_quantity
from mid.transactions as Z 
join mid.transaction_items_mop as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id where Y.item_value > 0
and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)>-24*3600
and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)<24*3600)AS X
group by substr(X.transaction_date,1,10),X.station_id,X.mop_name;

insert overwrite table res_HHTIPT
select B.substrb,B.station_id,A.count,A.sum,B.count-A.count,B.sum-A.sum,B.count,B.sum
from
(select substr(transaction_date,1,13)as substrb,station_id,count(transaction_id)as count,sum(sale_total)as sum
from mid.transactions
group by substr(transaction_date,1,13),station_id)as B
join
(select substr(Z.transaction_date,1,13)as substra,Z.station_id,count(Z.transaction_id)as count,sum(Z.sale_total)as sum
from
mid.transactions AS Z
JOIN mid.cashiers AS Y
on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id and Y.cashier_name = 'HHT'
group by substr(Z.transaction_date,1,13),Z.station_id) as A
on A.substra=B.substrb AND A.station_id=B.station_id;

insert overwrite table res_tr
select A.station_id,substr(A.transaction_date,1,7),sum(B.item_quantity)
from mid.transactions as A
join mid.transaction_items_oil as B
on A.transaction_id=B.transaction_id and A.station_id=B.station_id
where unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
group by A.station_id,substr(A.transaction_date,1,7);

set hive.auto.convert.join=false;

insert overwrite table res_mopipt
select substr(B.transaction_date,1,10),A.station_id,A.mop_name,count(A.item_quantity),sum(A.item_value)
from mid.transaction_items_mop as A
join
(select Z.station_id,Z.transaction_id,Z.transaction_date
from
mid.transactions AS Z
JOIN mid.cashiers AS Y
on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id and Y.cashier_name != 'HHT') as B
on A.transaction_id=B.transaction_id and A.station_id=B.station_id where A.item_value > 0
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
group by substr(B.transaction_date,1,10),A.station_id,A.mop_name;

set hive.auto.convert.join=false;

insert overwrite table res_mophht
select substr(B.transaction_date,1,10),A.station_id,A.mop_name,count(A.item_quantity),sum(A.item_value)
from mid.transaction_items_mop as A
join
(select Z.station_id,Z.transaction_id,Z.transaction_date
from
mid.transactions AS Z
JOIN mid.cashiers AS Y
on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id and Y.cashier_name = 'HHT') as B
on A.transaction_id=B.transaction_id and A.station_id=B.station_id where A.item_value > 0
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
group by substr(B.transaction_date,1,10),A.station_id,A.mop_name;

insert OVERWRITE table mid_exceptLube
select DISTINCT d.transaction_date,d.station_id,d.transaction_id,d.item_value from 
(
select A.transaction_id as transaction_id,C.transaction_date as transaction_date ,A.station_id as station_id,B.department_id as department_id,A.item_value as item_value 
from mid.transaction_items_notoil as A
join mid.products as B
on A.product_code = B.product_code and B.station_id = 50006
join mid.transactions as C
on C.transaction_id = A.transaction_id and C.station_id = A.station_id
where unix_timestamp(A.transaction_date)-unix_timestamp(C.transaction_date)>-24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(C.transaction_date)<24*3600 )d
where d.department_id not in (548,64);

insert overwrite table res_exceptLube
select substr(minutes,1,16),stationID,count(DISTINCT transactionID),sum(itemvalue)
from mid_exceptLube
group by substr(minutes,1,16),stationID;


